#!/usr/bin/env python2.6

import sqlobject
import sqlite3
import model
import os, time
import datetime

class db_helper:
    def __init__ (s, db_file):
        s.db_file = db_file
        os.rename(db_file, db_file + '.old')
        time.sleep(1)
        print 'db/%s' % db_file + '.old'
        s.old_conn = sqlite3.connect('%s.old' % db_file)
        s.old_conn.text_factory = str

        s.cursor = s.old_conn.cursor()
        db_connection_str = 'sqlite:' + db_file
        sqlobject.sqlhub.processConnection = sqlobject. connectionForURI(db_connection_str)
        model.note.createTable()
        model.tag.createTable()
        model.block.createTable()

    def upgrade(s):
        print 'I\'ll help you transform old DB into new one.'
        print 'process upgrading...'
        try:
            print 'copy notes from old DB to new DB'
            s.copy_notes()
            print 'copy blocks from old DB to new DB'
            s.copy_blocks()
            print 'copy tags from old DB to new DB'    
            s.copy_tags()
            print 'rebuild tags...'
            s.rebuild_tags()

        except Exception, e:
            print 'ERROR: %s' % str(e)
            print 'Reverting...'
            if os.path.exists('%s.old' % s.db_file):
                os.unlink(s.db_file)
                os.rename('%s.old' % s.db_file, s.db_file)
        pass

    def copy_notes(s):
        old_notes = s.cursor.execute('select * from note').fetchall()
        for n in old_notes:
            old_id, old_title = n
            nn = model.note(id=old_id,title=old_title)

    def copy_blocks(s):
        def transform_time(str):
            date, time = str.split(' ')
            ret = date.split('-')
            ret.extend(time.split(':'))
            ret = apply(datetime.datetime, [int(float(e)) for e in ret])
            return ret

        old_blocks = s.cursor.execute('select * from block').fetchall()
        for b in old_blocks:
            o_id, o_note_id, o_contect, o_pos, o_create_time, o_modify_time, o_fold = b            
            nn = list(model.note.selectBy(id=o_note_id))
            print nn
            nb = model.block(id= o_id,
                note=nn[0], content=o_contect, pos=o_pos,
                create_time = transform_time(o_create_time),
                modify_time = transform_time(o_modify_time),
                fold = o_fold
                )

    def copy_tags(s):
        old_ts = s.cursor.execute('select * from tag').fetchall()
        for t in old_ts:
            old_id, old_name, old_count = t
            nt = model.tag(id=old_id, name = old_name, count = old_count)


    def rebuild_tags(s):
        tag2block = s.cursor.execute('SELECT * FROM tag2block').fetchall()   
        for t2b in tag2block:
            id, o_tag_id, o_block_id = t2b
            nb = list(model.block.selectBy(id=o_block_id))
            nt = list(model.tag.selectBy(id=o_tag_id))
            nb[0].addTag(nt[0])
        pass


if __name__ == '__main__':
    db_url = os.path.abspath('db/ubinote.db.sqlite')
    print db_url
    if os.path.exists(db_url) and not os.path.exists(db_url+'.old'):
        # more work is needed to do database upgrading
        dbh = db_helper(db_url)
        dbh.upgrade()
    else:
        print 'nothing to do :)'



